package itsm.isperp.module.controller.db;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.utils.JsonBuilder;
import itsm.isperp.framework.web.response.ResultMessage;
import itsm.isperp.framework.web.response.view.ExcelView;
import itsm.isperp.module.dto.db.ExcelColumnToEntityField;
import itsm.isperp.module.entity.customer.CusCustomer;
import itsm.isperp.module.service.customer.CusCustomerService;
import itsm.isperp.module.service.db.DbEntityFieldService;
import itsm.isperp.module.service.db.DbEntityService;
import itsm.isperp.module.service.db.DbImpExpService;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

/**
 * 数据导入控制器
 * 
 * @author lizx
 * @date 2013-5-4
 */
@Controller
@RequestMapping("db/import")
public class DbImportController {

  @Autowired
  DbImpExpService dbImpExpService;

  @Autowired
  DbEntityService dbEntityService;

  @Autowired
  DbEntityFieldService dbEntityFieldService;

  /**
   * 导入页面
   * 
   * @return
   */
  @RequestMapping(value = { "manage", "edit" })
  public ModelAndView edit(String categoryName, String ty, String relationId) {
    ModelAndView mv = new ModelAndView("db/import/edit");
    mv.addObject("entities", dbEntityService.findAllUseForImport(categoryName));
    if (StringUtils.isNotEmpty(ty)) {
      mv.addObject("ty", ty);
      mv.addObject("relationId", relationId);
    }
    return mv;
  }

  @RequestMapping("ciEdit")
  public ModelAndView ciEdit(String categoryName, String ty, String relationId) {
    ModelAndView mv = new ModelAndView("db/import/ciEdit");
    mv.addObject("entities", dbEntityService.findAllUseForImport(categoryName));
    if (StringUtils.isNotEmpty(ty)) {
      mv.addObject("ty", ty);
      mv.addObject("relationId", relationId);
    }
    return mv;
  }

  @ResponseBody
  @RequestMapping("getExcelFileInfo")
  public String getExcelFileInfo(String entityName, String filePath)
      throws FileNotFoundException, IOException {

    String fileName = ContextHolder.getApplicationPath() + "/" + filePath;

    Map<String, List<ExcelColumnToEntityField>> map = this.dbImpExpService
        .getExcelFileInfo(entityName, fileName);

    return JsonBuilder.buildNormalBinder().toJson(map);

  }

  @ResponseBody
  @RequestMapping("getImportExcelModel")
  public String getImportExcelModel(String entityName, String entityCategoryId,
      String entityCategoryName) throws IOException {

    return this.dbImpExpService.generateImportExcelModel(entityName,
        entityCategoryId, entityCategoryName);
  }

  /**
   * 导入数据，并返回数据检查结果
   * 
   * @return
   * @throws SQLException
   */
  @ResponseBody
  @RequestMapping("imp")
  public ResultMessage imp(String filePath, String sheetName,
      String entityName, String importType, String map, String categoryId,
      String categoryName, String ty, String relationId) throws SQLException {

    String fileName = ContextHolder.getApplicationPath() + "/" + filePath;

    ExcelColumnToEntityField[] mitchedList = JsonBuilder.buildNormalBinder()
        .fromJson(map, ExcelColumnToEntityField[].class);

    List<ExcelColumnToEntityField> list = new ArrayList<ExcelColumnToEntityField>();

    if (mitchedList != null && mitchedList.length > 0) {
      for (int i = 0; i < mitchedList.length; i++)
        list.add(mitchedList[i]);
    }

    ResultMessage result = this.dbImpExpService.importExcelData(fileName,
        sheetName, importType, entityName, list, categoryId, categoryName);
    // 如果是入库导入资产，则 需要更新几个字段
    
    if(StringUtils.isNotEmpty(entityName) && entityName.equals("人员")
        && result.data != null)
    {
      List<String> codeList = (List) result.data;
      if(codeList.size()>0)
      {
        String codes = "";
        for (int i = 0; i < codeList.size(); i++) {
          codes += "'" + codeList.get(i).toString() + "',";
        }
        codes = codes.substring(0, codes.length() - 1);
        String sql = "update app_user a set customer_id=(select id from cus_customer where a.customer_name=name limit 1),"
                  +"password='WP5Essdoi1Q=' where name in("+codes+")";
        JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
        jdbcTemplate.execute(sql);
      }
    }
    
    if (StringUtils.isNotEmpty(ty) && ty.equals("sparepartIn")
        && result.data != null) {
      List<String> codeList = (List) result.data;
      String codes = "";
      for (int i = 0; i < codeList.size(); i++) {
        codes += "'" + codeList.get(i).toString() + "',";
      }

      codes = codes.substring(0, codes.length() - 1);
      String sql = "update spt_sparepart a set a.use_status=0,a.id=a.code,a.project_id=(select id from proj_project where name=a.project_name limit 1),manufacturer_provider_id=(select id from prd_provider where name=a.manufacturer_provider_name limit 1),"
          + "support_staff_id=(select name from app_user where fullname=a.support_staff_name limit 1),use_staff_id=(select name from app_user where fullname=a.use_staff_name limit 1) where a.id in("
          + codes + ")";
      JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
      jdbcTemplate.execute(sql);
      String sql2 = "INSERT INTO spt_sparepart_relation(id,sparepart_id,ci_id,type_key) SELECT UUID(),'"
          + relationId
          + "',id,'sparepartIn' FROM spt_sparepart WHERE CODE IN("
          + codes + ")";
      jdbcTemplate.execute(sql2);
    }
    return result;

  }

  @ResponseBody
  @RequestMapping("ciImp")
  public ResultMessage ciImp(String filePath, String sheetName,
      String entityName, String importType, String map, String categoryId,
      String categoryName, String ty, String relationId) throws SQLException {

    String fileName = ContextHolder.getApplicationPath() + "/" + filePath;

    ExcelColumnToEntityField[] mitchedList = JsonBuilder.buildNormalBinder()
        .fromJson(map, ExcelColumnToEntityField[].class);

    List<ExcelColumnToEntityField> list = new ArrayList<ExcelColumnToEntityField>();

    if (mitchedList != null && mitchedList.length > 0) {
      for (int i = 0; i < mitchedList.length; i++)
        list.add(mitchedList[i]);
    }

    ResultMessage result = this.dbImpExpService.importExcelData(fileName,
        sheetName, importType, entityName, list, categoryId, categoryName);
    // 如果是入库导入资产，则 需要更新几个字段
    if (StringUtils.isNotEmpty(ty) && ty.equals("sparepartIn")
        && result.data != null) {
      List<String> codeList = (List) result.data;
      String codes = "";
      for (int i = 0; i < codeList.size(); i++) {
        codes += "'" + codeList.get(i).toString() + "',";
      }
      // 获取本公司企业ID
      CusCustomerService cusCustomerService = ContextHolder
          .getSpringBean("cusCustomerService");
      CusCustomer cus = cusCustomerService.getCompanyCustomer();
      String cusName = cus.getName();
      String cusId = cus.getId();
      codes = codes.substring(0, codes.length() - 1);
      String sql = "update conf_ci set are_company=2,cus_customer_id='"
          + cusId
          + "',cus_customer_name='"
          + cusName
          + "',use_staff_id_temp=use_staff_id,use_staff_name_temp=use_staff_name,status_temp=status where code in("
          + codes + ")";
      JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
      jdbcTemplate.execute(sql);
      String sql2 = "INSERT INTO spt_sparepart_relation(id,sparepart_id,ci_id,type_key) SELECT UUID(),'"
          + relationId
          + "',id,'sparepartIn' FROM conf_ci WHERE CODE IN("
          + codes + ")";
      jdbcTemplate.execute(sql2);
    }

    return result;

  }

  /**
   * 下载导入模板
   * 
   * @return 下载地址
   * @throws IOException
   *           文件不存在
   */
  @ResponseBody
  @RequestMapping("downloadImportExcelModel")
  public ModelAndView downloadImportExcelModel(String entityName,
      String categoryId, String categoryName) throws IOException {
    String filePath = this.dbImpExpService.generateImportExcelModel(entityName,
        categoryId, categoryName);
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("filePath", filePath);
    return new ModelAndView(new ExcelView(), map);
  }
}
